package com.sapient;

import java.io.IOException;
import java.sql.*;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

/**
 * Servlet implementation class MarketEdit
 */
@WebServlet("/MarketEdit")
public class MarketEdit extends HttpServlet {
	String[] result = null;
	String[] result2 = null;
	String[] result3= null;
	String[] result4 = null;

	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	 public MarketEdit() {
		super();
		// TODO Auto-generated constructor stub
	 }

	 /**
	  * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	  */
	 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		String fromsearch =(String) request.getParameter("fromsesarch");
		 if(fromsearch!=null)
			 request.setAttribute("fromsearch", "");
		 int rows = 0;
		 String selected = request.getParameter("selected");
		 String marketid = (String) request.getParameter("marketid");
		 if(selected!=null)
			 marketid=selected;
		 ResultSet rs = null;
		 String[] marketdetails = null; ;
		 String[] mid = null;
		 Connection conn = null;   
			HttpSession session=request.getSession();
			String user = (String) session.getAttribute("role");
		 try
		 { 
			 Class.forName("oracle.jdbc.driver.OracleDriver");
			 conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
			 Statement st = conn.createStatement(); 
			 String SQL = "";
				if(user.equals("user"))
					 SQL = "select MarketName, location, commodityType, currency, marketid from Market where flag='N'";
				else
					 SQL = "select MarketName, location, commodityType, currency, marketid from Market";
				
			 rs= st.executeQuery(SQL);
			 while(rs.next()){
				 rows++;
			 }
			 rs= st.executeQuery(SQL);
			 result = new String[rows];
			 mid = new String[rows]; 
			 int count = 0;
			 while(rs.next())
			 {
				 result[count] = rs.getString(1) + "/" + rs.getString(2) +"/"+ rs.getString(3)+"/"+ rs.getString(4);
				 mid[count]=rs.getString(5);
				 count++; 
			 }
			 
			 if( marketid != null && !marketid.equals("null")){
				System.out.println("inside: "+marketid);
				System.out.println(marketid.equals("null"));
				 marketdetails = new String[9];
				 String get = "select marketname, location, commodityType, currency,marketid ,startdate,flag,enddate from market where marketid = '"+marketid+"'";
				 rs = st.executeQuery(get);
				 while(rs.next()){
					 marketdetails[0]=rs.getString(1);
					 marketdetails[1]=rs.getString(2);
					 marketdetails[2]=rs.getString(3);
					 marketdetails[3]=rs.getString(4);
					 marketdetails[4]=rs.getString(5);
					 marketdetails[5]=rs.getString(6).substring(0, 10);
					 marketdetails[6]=rs.getString(7);
					 String endDate = rs.getString(8);
					 if(endDate!=null){
							endDate = endDate.substring(0, 10);
			     	 marketdetails[7]=endDate;
					 }
					 else
					 {
						 marketdetails[7]="0000-00-00";
					 }
					 
					 }

				 //****LOCATION DATA***//*
				 rows = 0;
				 SQL = "select locationName from Location ";
				 rs= st.executeQuery(SQL);
				 while(rs.next()){
					 rows++;
				 }
				 result2 = new String[rows];
				 rs=st.executeQuery(SQL);
				 count = 0;
				 while(rs.next())
				 {
					 result2[count] = rs.getString(1);
					 count++; 
				 }

				 //****COMMODITY DATA***//*
				 rows = 0;

				 SQL = "select CommodityType from Commodity ";
				 rs= st.executeQuery(SQL);
				 while(rs.next()){
					 rows++;
				 }
				 result3 = new String[rows];
				 rs=st.executeQuery(SQL);
				 count = 0;
				 while(rs.next())
				 {
					 result3[count] = rs.getString(1);
					 count++; 
				 }

				 //****CURRENCY DATA***//*
				 rows = 0;

				 SQL = "select currencyName from Currency ";
				 rs= st.executeQuery(SQL);
				 while(rs.next()){
					 rows++;
				 }
				 result4 = new String[rows];
				 rs=st.executeQuery(SQL);
				 count = 0;
				 while(rs.next())
				 {
					 result4[count] = rs.getString(1);
					 count++; 
				 }
				 marketid=null;
			 }
			 conn.close(); 

		 } catch (Exception e) { 
			 System.err.println("Got an exception! "); 
			 System.err.println(e.getMessage()); 
			 e.printStackTrace();
		 } 
		 request.setAttribute("preselected", marketid);
		 request.setAttribute("mid", mid);
		 request.setAttribute("market", result);
		 request.setAttribute("marketdetails", marketdetails);
		 request.setAttribute("Location", result2);
		 request.setAttribute("Commodity", result3);
		 request.setAttribute("Currency", result4);
		 RequestDispatcher m = getServletContext().getRequestDispatcher("/MarketEdit.jsp");
		 m.forward(request, response);
	 }

	 /**
	  * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	  */
	 protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		 String MarketName = request.getParameter("MarketName");
		 String marketid = request.getParameter("marketid");
		 String Location = request.getParameter("selected1");
		 String CommodityType = request.getParameter("selected2");
		 String Currency = request.getParameter("selected3");
		 String StartDate = request.getParameter("StartDate");
		 String EndDate = request.getParameter("EndDate");
		 String SelectFlag = request.getParameter("SelectFlag");
		 String oldFlag=request.getParameter("oldFlag");
		 StartDate = StartDate.substring(0, 10);
		 String status = "";   
		 String SQL = "";
		  Connection conn = null;                
		 try
		 { 
			 Class.forName("oracle.jdbc.driver.OracleDriver");
			 conn = DriverManager.getConnection(Config.url,Config.username,Config.password); 
			 Statement st = conn.createStatement(); 
			 
			 String currencyFlag = "";
			 String locationFlag = "";
			 String commodityFlag = "";
			 if(!oldFlag.equals(SelectFlag)){
					if(SelectFlag.equals("N")){ //Yes to No
						String SQL1 = "select commoditytype,location,currency from market where marketid = "+marketid;
						ResultSet rs = st.executeQuery(SQL1);
						String[] s = null;
						s = new String[3];
						while(rs.next()){
						s[0] = rs.getString(1); //commodity
						s[1] = rs.getString(2); //location
						s[2] = rs.getString(3); //currency
						}
						String SQL2 = "select c.flag from commodity c where c.commoditytype = '"+s[0]+"'";
						System.out.println(SQL2);
						ResultSet rs2 = st.executeQuery(SQL2);
						while(rs2.next()){
							commodityFlag = rs2.getString(1); //commodity flag
							System.out.println("Commodity flag: "+commodityFlag);
						}
						
						SQL2 = "select l.flag from location l where l.locationname = '"+s[1]+"'";
						System.out.println(SQL2);
						rs2 = st.executeQuery(SQL2);
						while(rs2.next()){
							locationFlag = rs2.getString(1); //commodity flag
							System.out.println("location"+locationFlag);
						}
						
						SQL2 = "select cu.flag from currency cu where cu.currencyname = '"+s[2]+"'";
						System.out.println(SQL2);
						rs2 = st.executeQuery(SQL2);
						while(rs2.next()){
							currencyFlag = rs2.getString(1); //commodity flag
							System.out.println("currency"+currencyFlag);
						}
							if(currencyFlag.equals("N")|| locationFlag.equals("N") || commodityFlag.equals("N")){
								status = "Flag update failed, conflicting with other entities. ";
							}
							else{ 
								SQL2 = "update Market set flag = 'Y' where marketid = "+marketid;
								System.out.println(SQL2);
								st.executeUpdate(SQL2);
							}
						}
			 }
			 if(EndDate.equals("")){
				 SQL = "update Market set  marketname = '"+MarketName+ "',Location = '"+Location+ "',CommodityType = '"+CommodityType+ "',"
			 		+ "Currency = '"+Currency+ "', StartDate= to_date('"+StartDate+"','RRRR-MM-DD'), marketflag='"+SelectFlag+"' where marketid = '"+marketid+"'";
			 }
			 else{
				 SQL = "update Market set  marketname = '"+MarketName+ "',Location = '"+Location+ "',CommodityType = '"+CommodityType+ "',"
					+ "Currency = '"+Currency+ "', StartDate= to_date('"+StartDate+"','RRRR-MM-DD'),enddate= to_date('"+EndDate+"','RRRR-MM-DD') , marketflag='"+SelectFlag+"' where marketid = '"+marketid+"'";
			 }
			 st.executeUpdate(SQL);
			 status += "Record successfully updated!";

			 conn.close();
		 } catch (Exception e) { 
			 System.err.println("Got an exception! "); 
			 System.err.println(e.getMessage()); 
			 e.printStackTrace();
		 }
		 request.setAttribute("status", status);
		 doGet(request,response);
	 }

}
